#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
INSERT INTO
    edu_online_dws.sign_up_count
SELECT
    dt_year,
    dt_month,
    dt_day,
    origin_type,
    seo_source,
    dept_name,
    itcast_school_name,
    itcast_subject_name,
    --时间分区
    CASE
        WHEN grouping(dt_year) = 0 THEN 'year'--年
        WHEN grouping(dt_month) = 0 THEN 'month'--月
        WHEN grouping(dt_day) = 0 THEN 'day'--天
        ELSE NULL
    END AS time_type,
    --其他分区
    CASE
        WHEN grouping(origin_type) = 0 THEN 'ofline'--线上线下
        WHEN grouping(seo_source) = 0 THEN 'source'--来源渠道
        WHEN grouping(dept_name) = 0 THEN 'consultation'--咨询中心
        WHEN grouping(itcast_school_name) = 0 THEN 'school'--校区
        WHEN grouping(itcast_subject_name) = 0 THEN 'subject'--学科
        ELSE NULL
    END AS group_type,
    COUNT (payment_state) AS sign_up_num,
    COUNT (id) AS intention_num
FROM hive.edu_online_dwb.sign_up_wide
GROUP BY
GROUPING SETS (
    --年
    (dt_year),
    (dt_year,origin_type),--线上线下
    (dt_year,seo_source),--来源渠道
    (dt_year,dept_name),--咨询中心
    (dt_year,itcast_school_name),--校区
    (dt_year,itcast_subject_name),--学科
    (dt_year,itcast_school_name,itcast_subject_name),--校区&学科
    --月
    (dt_year,dt_month),
    (dt_year,dt_month,origin_type),--线上线下
    (dt_year,dt_month,seo_source),--来源渠道
    (dt_year,dt_month,dept_name),--咨询中心
    (dt_year,dt_month,itcast_school_name),--校区
    (dt_year,dt_month,itcast_subject_name),--学科
    (dt_year,dt_month,itcast_school_name,itcast_subject_name),--校区&学科
    --日
    (dt_year,dt_month,dt_day),
    (dt_year,dt_month,dt_day,origin_type),--线上线下
    (dt_year,dt_month,dt_day,seo_source),--来源渠道
    (dt_year,dt_month,dt_day,dept_name),--咨询中心
    (dt_year,dt_month,dt_day,itcast_school_name),--校区
    (dt_year,dt_month,dt_day,itcast_subject_name),--学科
    (dt_year,dt_month,dt_day,itcast_school_id,itcast_school_name,itcast_subject_name)--校区&学科
);